package com.gugee.tiktok.common.model.dal.ranking;

import com.gugee.tiktok.common.model.ranking.RankingRegion;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface RankingRegionRepository extends CrudRepository<RankingRegion,Long>,RankingRegionExtRepository {


    @Query("SELECT " +
            "b.id blogger_id,b.o_uid uid,b.o_nickname nickname,b.o_unique_id unique_id," +
            "b.o_avatar168 avatar,b.with_fusion_shop_entry,b.bio_url,b.bio_email," +
            "b.o_follower_count follower,b.enterprise_verify_reason,b.custom_verify,b.o_region region,b.o_total_favorited likes," +
            "sum( r.likes_growth ) likes_growth,sum( r.review_growth ) review_growth,sum( r.share_growth ) share_growth," +
            "sum( r.follower_growth ) follower_growth,sum( r.view_growth ) view_growth,r.blogger_type,ROUND(AVG(avg_view)) avg_view," +
            "IFNULL(ROUND(sum( b.o_avg_likes + b.o_avg_share + b.o_avg_reviews )/ sum( b.o_avg_views )* 100,1),0) engagement_rate,b.score " +
            "FROM " +
            "ranking_region r left join blogger_main b on r.uid = b.o_uid " +
            "WHERE " +
            "r.region = :region  and " +
            "r.ranking_type = :rankingType and " +
            "r.belong  BETWEEN :firstDate and :lastDate " +
            "group by r.uid " +
            "order by avg(r.score) desc " +
            "limit 0,1000")
    List<RankingRegion> findRankings(String region, int rankingType, int firstDate, int lastDate);

    List<RankingRegion> findByRegionAndRankingTypeAndBelongOrderByScoreDesc(String region,int rankingType, int belong);
}
